package org.iteam.db.sql;

import java.text.MessageFormat;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.iteam.db.sql.exception.SqlException;

public class SqlLoader {
	public static Map<String, String> sqlMap = new HashMap<String, String>();
	static {
		init();
	}

	public static void init() {
		SAXReader reader = new SAXReader();
		try {
			Document document = reader.read(Class.class.getResource("/")
					.getPath() + "sql.xml");
			Element root = document.getRootElement();
			for (Iterator<?> i = root.elementIterator("package"); i.hasNext();) {
				Element packageElement = (Element) i.next();
				String packageId = packageElement.attributeValue("id");
				for (Iterator<?> j = packageElement.elementIterator("sql"); j
						.hasNext();) {
					Element sqlElement = (Element) j.next();
					String sqlId = sqlElement.attributeValue("id");
					if (sqlId == null || sqlId.equalsIgnoreCase(""))
						throw new SqlException("SQL定义-ID为空");
					String sql = sqlElement.getTextTrim();
					String key = "";
					if (packageId == null || packageId.equals("")) {
						key = sqlId;
					} else {
						key = packageId + "." + sqlId;
					}
					if (sqlMap.containsKey(key))
						throw new SqlException("SQL定义-ID重复");
					sqlMap.put(key, sql);
				}
			}

		} catch (DocumentException e) {
			e.printStackTrace();
		} catch (SqlException e) {
			e.printStackTrace();
		}

	}

	public static String SQL(String key) {
		try {
			if (sqlMap.containsKey(key))
				return sqlMap.get(key);
			else
				throw new SqlException("SQL定义-不存在ID");
		} catch (SqlException e) {
			e.printStackTrace();
			return "";
		}
	}

	public static String SQLFormat(String key, Object... k) {
		try {
			if (sqlMap.containsKey(key)) {
				String sql = sqlMap.get(key);
				MessageFormat form = new MessageFormat(sql);
				sql = form.format(k);
				return sql;
			} else
				throw new SqlException("SQL定义-不存在ID");
		} catch (SqlException e) {
			e.printStackTrace();
			return "";
		}
	}

	/**
	 * 转换为SQL-Oracle分页
	 * 
	 * @param sql
	 * @return
	 */
	public static String toOraclePage(String sql) {
		return "SELECT * FROM (SELECT MY_TABLE.*,ROWNUM TABLE_SN FROM (" + sql
				+ ") MY_TABLE WHERE ROWNUM <=?) WHERE TABLE_SN>?";
	}

	public static String toMysqlPage(String sql) {
		return sql + " LIMIT ?,?";
	}
}
